package models;

import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcessShipmentModel extends AbstractModel {

	private int shipId = -1;
	private String supName;
	private String sname;
	
	public boolean validateShipmentInfo(String supName, String sname) {
		try {
			ps = con.prepareStatement("SELECT * FROM Supplier WHERE supName = ?");
			ps.setString(1, supName);
			boolean validSupName = ps.executeQuery().next();
			
			ps = con.prepareStatement("SELECT * FROM Store WHERE sname = ?");
			ps.setString(1, sname);
			if (validSupName && ps.executeQuery().next()) {
				this.supName = supName;
				this.sname = sname;
				return true;
			}
			else return false;
		} catch (SQLException ex) {
			return registerException(ex);
		}
	}

	public boolean validateUPC(int upc) {
		try {
			ps = con.prepareStatement("SELECT * FROM Item WHERE upc = ?");
			ps.setInt(1, upc);
			return ps.executeQuery().next();
		} catch (SQLException ex) {
			return registerException(ex);
		}
	}
	
	private int generateShipId() {
		try {
			ps = con.prepareStatement("INSERT INTO Shipment VALUES (shipmentId_counter.nextval, ?, ?,sysdate)");
			ps.setString(1, supName);
			ps.setString(2, sname);
			
			ps.executeUpdate();
			ps = con.prepareStatement("SELECT shipmentId_counter.currval FROM dual");
			ResultSet rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1);
		} catch(SQLException ex) { ex.printStackTrace(); return -1; }
	}
	
	public boolean processShippedItem(int upc, int quantity, int price) {
		if (shipId == -1) shipId = generateShipId();
		
		try {
			//Check to see if item exists in store already
			ps = con.prepareStatement("SELECT stock FROM Stored WHERE sname = ? AND upc = ? ");
			ps.setString(1, sname);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			//if item exists, increment the quantity accordingly
			//if not, add item to store with shipped quantity
			if (rs.next()) {
				int newStock = rs.getInt("stock") + quantity;
				
				//Update stock in store
				ps = con.prepareStatement("UPDATE Stored SET stock = ? WHERE sname = ? AND upc = ? ");
				ps.setInt(1, newStock);
				ps.setString(2, sname);
				ps.setInt(3, upc);
				ps.executeUpdate();
			}
			else {
				ps = con.prepareStatement("INSERT INTO Stored VALUES(?,?,?)");
				ps.setString(1, sname);
				ps.setInt(2, upc);
				ps.setInt(3, quantity);
				ps.executeUpdate();
			}
			
			//Update the sell price of the item
			ps = con.prepareStatement("UPDATE Item SET sellPrice = ? WHERE upc = ?");
			ps.setInt(1, (int)(price*1.2));
			ps.setInt(2, upc);
			ps.executeUpdate();
			
			ps = con.prepareStatement("INSERT INTO ShipItem VALUES (?,?,?,?)");
			ps.setInt(1, shipId);
			ps.setInt(2, upc);
			ps.setInt(3, price);
			ps.setInt(4, quantity);
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			try { con.rollback(); }
			catch (SQLException ex1) { ex1.printStackTrace(); registerException(ex1); }
			return registerException(ex);
		}
		return true;
		
	}
	
	public boolean commit() {
		try {
			shipId = -1;
			con.commit();
			return true;
		} catch (SQLException ex) {
			return registerException(ex);
		}
		
	}

	@Override
	public ResultSet show() {
		// TODO Auto-generated method stub
		return null;
	}

}
